package test.excel;

import com.alibaba.fastjson.JSON;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ReadAndWriteExcel2 {

    @Data
    static class SearchData{
        private String cid ;
        private String tenantName ;
        private String shopName ;
    }

    public static void readAndWrite(String readFilePath,int readStartRow, int writeStartRow) throws Exception{
        List<SearchData> searchDataList = read(readFilePath, readStartRow);

        List<String> filterList = new ArrayList<>();
        for (SearchData searchData : searchDataList) {
            if(! filterList.contains(searchData.getCid() + "L")){
                filterList.add(searchData.getCid() + "L");
            }
        }

        System.out.println(JSON.toJSONString(filterList));

        //write(searchDataList, writeStartRow);
    }

    private static List<SearchData> read(String filePath,int startRow) throws Exception{

        List<SearchData> list = new ArrayList<>();

        File file = new File(filePath);

        String fileName = file.getName();
        boolean is2003 = true;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            is2003 = false;
        }
        InputStream ins = null;
        try {
            ins = new FileInputStream(file);
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (is2003) {
                wb = new HSSFWorkbook(ins);
            } else {
                wb = new XSSFWorkbook(ins);
            }
            Sheet sheet = wb.getSheetAt(0);

            for (int i = startRow ; i <= sheet.getLastRowNum(); i++) {

                Row row = sheet.getRow(i);

                String cid = getCellValue(row.getCell(0));
                String tenant_name = getCellValue(row.getCell(1));
                String shop_name = getCellValue(row.getCell(2));

//                String sql2 = String.format(" OR (a.shop_name = '%s' AND b.tenant_name = '%s') ", shop_name, tenant_name);
//                System.out.println(sql2);

                SearchData searchData = new SearchData();
                searchData.setCid(cid);
                searchData.setTenantName(tenant_name);
                searchData.setShopName(shop_name);

                list.add(searchData);
            }

        } catch (IOException e) {
            throw e;
        } finally {
            ins.close();
        }

        return list ;
    }

    private static void write(List<SearchData> searchDataList , int startRow) throws Exception{

        File file = new File("/Users/zhangrui/internalcolleagues.xlsx");

        String fileName = file.getName();
        boolean is2003 = true;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            is2003 = false;
        }

        Workbook wb = null;

        InputStream ins = null;
        try {
            ins = new FileInputStream(file);

            //根据文件格式(2003或者2007)来初始化
            if (is2003) {
                wb = new HSSFWorkbook(ins);
            } else {
                wb = new XSSFWorkbook(ins);
            }
            Sheet sheet = wb.getSheetAt(0);

            for (int i = startRow ; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);

                String tenant_name = getCellValue(row.getCell(0));
                String shop_name = getCellValue(row.getCell(5));

                for (SearchData searchData : searchDataList) {

                    String sql2 = String.format(" shop_name = '%s' , tenant_name = '%s' , sss=%s, ttt=%s ", shop_name, tenant_name, searchData.getTenantName() , searchData.getShopName());
                    //System.out.println(sql2);

                    if(searchData.getTenantName().equals(tenant_name) && searchData.getShopName().equals(shop_name)){

                        System.out.println(searchData.getCid());

                        //往第7列"cid"中写入对应的cid
                        Cell cell = row.getCell(6);
                        cell.setCellValue(searchData.getCid());
                    }
                }
            }

        } catch (IOException e) {
            throw e;
        } finally {
            ins.close();
        }

        FileOutputStream outs = null ;
        try {
            outs = new FileOutputStream(file);
            wb.write(outs);
            outs.flush();
        } catch (IOException e) {
            throw e ;
        } finally {
            outs.close();
        }

    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    private static boolean isHaveChinese(String str) {
        boolean flag = true;
        if (StringUtils.isEmpty(str)) {
            return flag;
        }
        int count = 0;
        String regEx = "[\\u4e00-\\u9fa5]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);

        while (m.find()) {
            for (int i = 0; i <= m.groupCount(); i++) {
                count = count + 1;
            }
        }
        if (count == 0) {
            flag = false;
        }
        return flag;
    }



    public static void main(String[] args) {

        try {
            ReadAndWriteExcel2.readAndWrite("/Users/zhangrui/Yearning_Data.xlsx",1 , 3);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
